import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

public class DepositDBManager extends DepositManager {

	private static final Long MILLS_IN_DAY = new Long(86400000);

	public DepositDBManager(Connection initConnection, PropertyDBManager propertyDBManager, DepositTypesDBManager depositTypesDBManager, BalanceDBManager balanceDBManager, AccountDBManager accountDBManager) {
		// constructor
		super(initConnection, propertyDBManager, depositTypesDBManager, balanceDBManager, accountDBManager);

	}

	@Override
	public boolean addDeposit(Deposit deposit) throws DBManagerException {

		PropertyDBManager propertyDBManager = getPropertyDBManager();
		DepositTypesDBManager depositTypesDBManager = getDepositTypesDBManager();
		BalanceDBManager balanceDBManager = getBalanceDBManager();
		AccountDBManager accountDBManager = getAccountDBManager();
		boolean flag = false;
		Connection conn = getConnection();
		PreparedStatement prest = null;

		Long custId = deposit.getCustId();
		double amount = deposit.getAmount();

		// Withdraw from account
		Account currAccount = accountDBManager.getAccountById(custId);

		if (accountDBManager.withdrawWithoutComm(currAccount, amount)) {

			// Add to balance
			Balance currBalance = new Balance(2, amount, custId);
			balanceDBManager.addBalance(currBalance);

			// Add Deposit
			try {

				String sql = ("INSERT INTO deposits (deposit_id, deposit_type, cust_id, openning_date, ending_date, last_calc_date, amount, estimated_amount, final_estimated_amount, interest_rate) VALUES (?,?,?,?,?,?,?,?,?,?)");

				prest = conn.prepareStatement(sql);

				// Get next DepositId
				Long depositId = propertyDBManager.getNextValue("deposit_sequence");

				Long depositType = deposit.getDepositeType();

				// Get Deposit Type Properties
				DepositType depositTypeRow = depositTypesDBManager.getDepositTypesObject(depositType);

				double interestRate = depositTypeRow.getInterestRateChange();
				deposit.setInterestRate(interestRate);
				calcFinalEstimatedAmount(deposit);

				Date openingDate = deposit.getOpenningDate();
				Date endingDate = deposit.getEndingDate();

				double estimatedAmount = deposit.getEstimatedAmount();
				double finalEstimatedAmount = deposit.getFinalEstimatedAmount();

				// setLong not working properly with Access
				prest.setBigDecimal(1, BigDecimal.valueOf(depositId));
				prest.setBigDecimal(2, BigDecimal.valueOf(depositType));
				prest.setBigDecimal(3, BigDecimal.valueOf(custId));
				prest.setDate(4, openingDate);
				prest.setDate(5, endingDate);
				prest.setDate(6, openingDate);
				prest.setDouble(7, amount);
				prest.setDouble(8, estimatedAmount);
				prest.setDouble(9, finalEstimatedAmount);
				prest.setDouble(10, interestRate);

				prest.executeUpdate();

				flag = true;
				System.out.println("[DepositDBManager]: Deposit was added succsesfuly.");

			} catch (final Exception e) {
				throw new DBManagerException("[DepositDBManager]: Error in data, cannot add deposit.");
			} finally {
				try {
					prest.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}

			}

		}
		return flag;
	}

	@Override
	public boolean deleteDeposit(Long depositId) throws DBManagerException {
		boolean flag = false;
		Connection conn = getConnection();
		BalanceDBManager balanceDBManager = getBalanceDBManager();
		AccountDBManager accountDBManager = getAccountDBManager();
		PreparedStatement prest = null;

		Deposit deposit = getDepositById(depositId);

		try {

			prest = conn.prepareStatement("DELETE FROM deposits WHERE deposit_id=?");

			prest.setBigDecimal(1, BigDecimal.valueOf(deposit.getDepositId()));

			prest.executeUpdate();

			// Return money to account
			Account currAccount = accountDBManager.getAccountById(deposit.getCustId());
			accountDBManager.depositWithoutComm(currAccount, deposit.getEstimatedAmount());

			// Add record to balance
			Balance currBalance = new Balance(2, -1 * deposit.getEstimatedAmount(), deposit.getCustId());
			balanceDBManager.addBalance(currBalance);

			flag = true;
			System.out.println("[DepositDBManager]: Deposit was deleted succsesfuly");
		} catch (final Exception e) {
			throw new DBManagerException("[DepositDBManager]: Error in data, cannot delete deposit.");
		} finally {
			try {
				prest.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}

		return flag;

	}

	@Override
	public Deposit getDepositById(long depositId) throws DBManagerException {
		// Receive Deposit object by deposit id number

		ResultSet rset = null;
		Statement statement = null;
		Deposit deposit = new Deposit(); // create new Deposit Object
		Connection conn = getConnection();// receive connection from Account
		// Manager

		try {

			statement = conn.createStatement();
			String query = ("SELECT deposit_id, deposit_type, cust_id, openning_date, ending_date, amount, estimated_amount, final_estimated_amount, interest_rate, last_calc_date" + " FROM deposits WHERE deposit_id =" + depositId);
			rset = statement.executeQuery(query);

			if (rset.next()) {
				deposit.setDepositId(rset.getLong(1));
				deposit.setDepositeType(rset.getLong(2));
				deposit.setCustId(rset.getLong(3));
				deposit.setOpenningDate(rset.getDate(4));
				deposit.setEndingDate(rset.getDate(5));
				deposit.setAmount(rset.getDouble(6));
				deposit.setEstimatedAmount(rset.getDouble(7));
				deposit.setFinalEstimatedAmount(rset.getDouble(8));
				deposit.setInterestRate(rset.getDouble(9));
				deposit.setLastCalcDate(rset.getDate(10));

			} else {

				System.err.println("[DepositDBManager]: Haven't row in DB with deposit_id =" + depositId);
				System.err.println("[DepositDBManager]: Return empty Object - please check deposit_id.");
			}
		} catch (SQLException e) {
			throw new DBManagerException("[DepositDBManager]: Can't get deposit with deposit_id =" + depositId);
		} finally {
			try {
				rset.close();
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}

		return deposit;
	}

	@Override
	public List getDepositByCustId(long custId) {
		// Receive Deposit object by deposit id number

		ResultSet rset = null;
		Statement statement = null;
		Connection conn = getConnection();// receive connection from Account
		ArrayList depositsList = new ArrayList();
		// Manager

		try {

			statement = conn.createStatement();
			String query = ("SELECT deposit_id, deposit_type, cust_id, openning_date, ending_date, amount, estimated_amount, final_estimated_amount, interest_rate, last_calc_date FROM deposits WHERE cust_id =" + custId);
			rset = statement.executeQuery(query);

			while (rset.next()) {

				Deposit deposit = new Deposit(); // create new Deposit Object

				deposit.setDepositId(rset.getLong(1));
				deposit.setDepositeType(rset.getLong(2));
				deposit.setCustId(rset.getLong(3));
				deposit.setOpenningDate(rset.getDate(4));
				deposit.setEndingDate(rset.getDate(5));
				deposit.setAmount(rset.getDouble(6));
				deposit.setEstimatedAmount(rset.getDouble(7));
				deposit.setFinalEstimatedAmount(rset.getDouble(8));
				deposit.setInterestRate(rset.getDouble(9));
				deposit.setLastCalcDate(rset.getDate(10));

				depositsList.add(deposit);
				deposit = null;
			}

		} catch (SQLException e) {
			depositsList = null;
			System.err.println("[DepositDBManager]: Can't read deposits by CustomerID: " + custId);

		} finally {
			try {
				rset.close();
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}

		return depositsList;
	}

	@Override
	public boolean calcEstimatedAmount(Deposit deposit, Calendar today) throws DBManagerException {

		DepositTypesDBManager depositTypesDBManager = getDepositTypesDBManager();
		Long changePeriodInMills, diffPeriod;

		// Check if it's time to recalc the Estimated Amount
		DepositType depositType = depositTypesDBManager.getDepositTypesObject(deposit.getDepositeType());
		changePeriodInMills = depositType.getChangePeriod() * MILLS_IN_DAY;
		diffPeriod = today.getTimeInMillis() - deposit.getLastCalcDate().getTime();

		if (diffPeriod >= changePeriodInMills) {
			double newEstimatedAmount = deposit.getAmount() + deposit.getAmount() * deposit.getInterestRate();
			deposit.setEstimatedAmount(newEstimatedAmount);
		}

		// Updating deposit after calculation

		return updateDeposit(deposit);
	}

	@Override
	public Deposit calcFinalEstimatedAmount(Deposit deposit) throws DBManagerException {
		// Only for deposits with fixed rate interest (type 3)

		DepositTypesDBManager depositTypesDBManager = getDepositTypesDBManager();
		DepositType depositType;
		Long changePeriodInMills, periodsToCalc;
		Double finalEstimatedAmount;

		if (deposit.getDepositeType() == 3) {

			// Calc periods
			depositType = depositTypesDBManager.getDepositTypesObject(3);
			changePeriodInMills = depositType.getChangePeriod() * MILLS_IN_DAY;
			periodsToCalc = (deposit.getEndingDate().getTime() - deposit.getOpenningDate().getTime()) / changePeriodInMills;

			// Calculate Future Final Estimated Amount

			finalEstimatedAmount = deposit.getAmount() * Math.pow(1 + deposit.getInterestRate(), periodsToCalc);

			deposit.setFinalEstimatedAmount(finalEstimatedAmount);
		}

		return deposit;
	}

	@Override
	public boolean drawDeposit(Deposit deposit) throws DBManagerException {
		boolean flag = false;
		DepositTypesDBManager depositTypesDBManager = getDepositTypesDBManager();
		BalanceDBManager balanceDBManager = getBalanceDBManager();
		DepositType depositType;
		Double penalty;

		// Calculating Penalty
		depositType = depositTypesDBManager.getDepositTypesObject(deposit.getDepositeType());
		penalty = deposit.getEstimatedAmount() * depositType.getClosePenalty();
		deposit.setEstimatedAmount(deposit.getEstimatedAmount() - penalty);

		updateDeposit(deposit);

		// Add Penalty to Balance
		Balance currBalance = new Balance(3, penalty, deposit.getCustId());
		balanceDBManager.addBalance(currBalance);

		// Deleting Deposit
		deleteDeposit(deposit.getDepositId());

		return flag;
	}

	@Override
	public boolean updateDeposit(Deposit deposit) throws DBManagerException {

		boolean flag = false;
		Connection conn = getConnection();
		PreparedStatement prest = null;

		try {

			String sql = "UPDATE deposits SET deposit_type = ?, cust_id = ?, openning_date = ?, ending_date = ?, amount = ?, estimated_amount = ?, final_estimated_amount = ?, interest_rate = ? WHERE deposit_id = ?";

			prest = conn.prepareStatement(sql);

			// Get next DepositId
			Long depositId = deposit.getDepositId();
			Long depositType = deposit.getDepositeType();
			Long custId = deposit.getCustId();
			Date openingDate = deposit.getOpenningDate();
			Date endingDate = deposit.getEndingDate();
			double amount = deposit.getAmount();
			double estimatedAmount = deposit.getEstimatedAmount();
			double finalEstimatedAmount = deposit.getFinalEstimatedAmount();
			double interestRate = deposit.getInterestRate();

			// setLong not working properly with Access

			prest.setBigDecimal(1, BigDecimal.valueOf(depositType));
			prest.setBigDecimal(2, BigDecimal.valueOf(custId));
			prest.setDate(3, openingDate);
			prest.setDate(4, endingDate);
			prest.setDouble(5, amount);
			prest.setDouble(6, estimatedAmount);
			prest.setDouble(7, finalEstimatedAmount);
			prest.setDouble(8, interestRate);
			prest.setBigDecimal(9, BigDecimal.valueOf(depositId));

			prest.executeUpdate();

			flag = true;
			System.out.println("[Deposit] Deposit was updated succsesfuly");
		} catch (final Exception e) {

			throw new DBManagerException("[DepositDBManager]: Error in data, cannot update deposit.");
		} finally {
			try {
				prest.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}

		return flag;

	}
}
